package com.hqyj.dao;

import com.hqyj.entity.Achievement;
import com.hqyj.entity.Check;
import com.hqyj.entity.Info;
import com.hqyj.entity.Info1;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;

@Repository
public interface AchievementDao extends JpaRepository<Achievement,Integer> {

    @Query(value = "SELECT COUNT(infostate) from info WHERE infostate=1;",nativeQuery = true)
    int getinfonum();

    @Query(value = "SELECT NAME FROM `user` WHERE id=:id",nativeQuery = true)
    String getname(@Param("id") Integer id);

    @Query(value = "SELECT achievement_id,a.house_id,a.user_id,name,housename,houseaddress,housesize,housetype,houseprice,a.year,a.mounth FROM achievement a,house h,user u WHERE u.id=a.user_id and a.house_id=h.house_id and a.user_id=:id",nativeQuery = true)
    List<Achievement> QueryACById(@Param("id") Integer id);

    @Query(value = "SELECT * from info i,user u where i.user_id=u.id AND i.infostate=1",nativeQuery = true)
    Page<Map<String, Info1>> getInfo(Pageable pageable);

    @Transactional
    @Modifying
    @Query(value = "UPDATE info SET infostate=0 WHERE info_id=:info_id",nativeQuery = true)
    int inforeay(@Param("info_id") Integer info_id);

    @Query(value = "select count( *) from `check` where user_id=:id and checkmounth=:mounth and checkyear=:year",nativeQuery = true)
    double FindCheak(@Param("id") Integer id,@Param("mounth") Integer mounth,@Param("year") Integer year);

    @Query(value = "SELECT ifnull(sum(houseprice),0) FROM achievement a,house h,user u WHERE u.id=a.user_id and a.house_id=h.house_id and a.user_id=:id AND a.mounth= :mounth AND a.year= :year",nativeQuery = true)
    double GetMoney(@Param("id") Integer id,@Param("mounth") Integer mounth,@Param("year") Integer year);


    @Query(value = "SELECT id,`name`,sex,phone,checkstate,reason,checkyear,checkmounth,checkday FROM `check` c,`user` u WHERE c.user_id=u.id;",nativeQuery = true)
    List<Map<String,Check>> QueryCheck();

    @Transactional
    @Modifying
    @Query(nativeQuery = true,value = "INSERT INTO `check` VALUES(null,:user_id,:checkstate,:reason,:checkyear,:checkmounth,:checkday)")
    int insertCheck(@Param("user_id") Integer user_id,@Param("checkstate")String checkstate,@Param("reason")String reason,@Param("checkyear")String checkyear,@Param("checkmounth")String checkmounth,@Param("checkday")String checkday);
}
